﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Serialization;
using Dapper;

namespace SMDB_Connect.SMDB
{

    #region 储存过程的Struct

    internal struct SP_ACCOUNT_LOGIN
    {
        internal static string spName = "smsp_Account_Login";
        internal static string spParaAccName = "@AccName";
        internal static string spParaAccPass = "@AccPass";
    }

    internal struct SP_PUSH_NEW
    {
        internal static string spName = "smsp_Push_New";
        internal static string spType = "@type";
        internal static string spAppro = "@approval";
        internal static string spAct = "@active";
        internal static string spMsg = "@msg";
        internal static string spPubDate = "@pub_date";
        internal static string spStart = "@start";
        internal static string spEnd = "@end";
        internal static string spTntID = "@tntid";
        internal static string spPushName = "@name";
        internal static string spProdID = "@prodid";
    }

    internal struct SP_PUSH_EDIT
    {
        internal static string spName = "smsp_Push_Update";
        internal static string spID = "@id";
        internal static string spType = "@type";
        internal static string spAppro = "@approval";
        internal static string spAct = "@active";
        internal static string spMsg = "@msg";
        internal static string spPubDate = "@pub_date";
        internal static string spStart = "@start";
        internal static string spEnd = "@end";
        internal static string spPushName = "@name";
        internal static string spProdID = "@prodid";
    }

    internal struct SP_RES_UPLOAD
    {
        internal static string spName = "smsp_Res_Upload";
        internal static string spID = "@id";
        internal static string spPath = "@path";
    }

    internal struct SP_RES_GETBYPUSH
    {
        internal static string spName = "smsp_Res_GetByPush";
        internal static string spPushID = "@push";
    }

    internal struct SP_PUSH_GETBYID
    {
        internal static string spName = "smsp_Push_GetByID";
        internal static string tntID = "@id";
    }

    internal struct SP_PUSH_GETACTIVE
    {
        internal static string spName = "smsp_Push_GetActiveByID";
        internal static string tntID = "@id";
    }

    internal struct SP_PUSH_UPDATEACT
    {
        internal static string spName = "smsp_Push_UpdateActStatus";
        internal static string pushID = "@id";
        internal static string status = "@status";
    }

    internal struct SP_PUSH_GETBYPUSHID
    {
        internal static string spName = "smsp_Push_GetByPushID";
        internal static string pushID = "@id";
    }

    internal struct SP_RES_UPDATE
    {
        internal static string spName = "smsp_Res_Update";
        internal static string pidcID = "@picid";
        internal static string newPath = "@path";
    }

    internal struct SP_ACCOUNT_GETBYID
    {
        internal static string spName = "smsp_Account_GetByID";
        internal static string accID = "@id";
    }

    internal struct SP_STORE_GETALL
    {
        internal static string spName = "smsp_Store_GetAll";
    }

    internal struct SP_STORE_GETBYACC
    {
        internal static string spName = "smsp_Store_GetByAcc";
        internal static string accID = "@id";
    }

    #endregion

    #region 数据库中表的实体类

    public class DBOAccount
    {
        /// <summary>
        /// 主键ID
        /// </summary>
        public int Acc_ID { get; set; }

        /// <summary>
        /// 账户名
        /// </summary>
        public string Acc_Name { get; set; }

        /// <summary>
        /// 账户密码
        /// </summary>
        public string Acc_Pass { get; set; }

        /// <summary>
        /// 联系手机（目前可忽略掉）
        /// </summary>
        public string Acc_Cell { get; set; }

        /// <summary>
        /// 联系工作电话（可忽略）
        /// </summary>
        public string Acc_WorkPhone { get; set; }

        /// <summary>
        /// 联系家庭电话（可忽略）
        /// </summary>
        public string Acc_HomePhone { get; set; }

        /// <summary>
        /// 账户等级，请参看AccountLevel枚举类
        /// </summary>
        public string Acc_Level { get; set; }

        /// <summary>
        /// 联系地址（可忽略）
        /// </summary>
        public string Acc_Add { get; set; }

        public string Acc_StoreName { get; set; }

        public string Acc_StoreImg { get; set; }

        /// <summary>
        /// 账户等级枚举，admin为商场管理者，tenant为商户，商铺租赁人，member为商场客人中的会员（暂无任何实际业务逻辑与之挂钩）
        /// </summary>
        public enum AccountLevel
        {
            Admin,
            Tenant,
            Member
        }
    }

    [DataContract]
    public class DBOPush
    {
        /// <summary>
        /// 推送信息的类型，请参看PushTypes枚举类
        /// </summary>
        [DataMember]
        public string Push_Type { get; set; }

        /// <summary>
        /// 推送信息的审批状态，请参看ApprovalStatus枚举类
        /// </summary>
        public string Push_Approval { get; set; }

        /// <summary>
        /// 推送信息的活跃状态，请参看ActiveStatus枚举类
        /// </summary>
        public string Push_Active { get; set; }

        /// <summary>
        /// 商户自定义推送文字信息，请控制在250字以内
        /// </summary>
        [DataMember]
        public string Push_Msg { get; set; }

        /// <summary>
        /// 推送信息的发布日期
        /// </summary>
        public DateTime Push_Pub_Date { get; set; }

        /// <summary>
        /// 限时推送信息的开始时间戳
        /// </summary>
        public DateTime Push_Start { get; set; }

        /// <summary>
        /// 限时推送信息的结束时间戳
        /// </summary>
        public DateTime Push_End { get; set; }

        /// <summary>
        /// 发布该推送信息的账户ID
        /// </summary>
        public int Acc_ID { get; set; }

        /// <summary>
        /// 该推送信息的自定义名字
        /// </summary>
        [DataMember]
        public string Push_Name { get; set; }

        /// <summary>
        /// 该推送信息关联产品的ID
        /// </summary>
        [DataMember]
        public string Push_Prod_ID { get; set; }

        [DataMember]
        public int Push_ID { get; set; }

        public string Push_Res { get; set; }

        /// <summary>
        /// 推送信息类型的枚举类
        /// </summary>
        public enum PushTypes
        {
            /// <summary>
            /// 打折类推送信息
            /// </summary>
            Discount,

            /// <summary>
            /// 新品类推送信息
            /// </summary>
            NewProd,

            /// <summary>
            /// 商场活动类推送信息
            /// </summary>
            Activities
        }

        /// <summary>
        /// 推送信息的审批状态枚举类
        /// </summary>
        public enum ApprovalStatus
        {
            Yes, // 已审核
            No, //待审核
            Rejected, //审核未通过
            Saved //储存
        }

        /// <summary>
        /// 推送信息的活跃状态的枚举类
        /// </summary>
        public enum ActiveStatus
        {
            /// <summary>
            /// 处于激活状态中
            /// </summary>
            Acted,

            /// <summary>
            /// 处于非激活状态中
            /// </summary>
            DeActed,

            /// <summary>
            /// 处于被删除状态中
            /// </summary>
            Deleted
        }
    }

    public class DBOPushRes
    {
        public int Res_ID { get; set; }

        public string Res_Path { get; set; }

        public int Push_ID { get; set; }
    }

    public class DBOStore
    {
        /// <summary>
        /// 数据库中的主键
        /// </summary>
        public int Store_ID;

        /// <summary>
        /// 店铺的物理位置信息（将来或许用来做寻路等复杂算法）
        /// </summary>
        public string St_Location;

        /// <summary>
        /// 店铺的名字
        /// </summary>
        public string St_Name;

        /// <summary>
        /// 店铺关联的管理账户的ID
        /// </summary>
        public int Acc_ID;

        /// <summary>
        /// 店铺的logo图片
        /// </summary>
        public string St_Logo;

        public string St_Description;
    }

    #endregion

    public class SMDB_Mgr
    {
        private SqlConnection sqlConnection;
        public static SqlConnectionStringBuilder connectionString;

        public SMDB_Mgr()
        {
            connectionString = new SqlConnectionStringBuilder
            {
                DataSource = @"192.168.0.174",
                NetworkLibrary = "dbmssocn ",
                InitialCatalog = "SM_MasterDB",
                UserID = "testadmin",
                Password = "12345678i"
            };
            sqlConnection = new SqlConnection(connectionString.ToString());
        }

        /// <summary>
        /// 登陆检查，目前还未添加hash等加密功能
        /// </summary>
        /// <param name="accName">用户名</param>
        /// <param name="pass">密码</param>
        /// <returns>若登陆成功则返回一个DBOAccount实例，若失败(密码或账户不匹配)则返回null.若出现其他问题(数据库端)则返回exception中的字符串信息</returns>
        public dynamic LoginCheck(string accName, string pass)
        {
            var p = new DynamicParameters();
            p.Add(SP_ACCOUNT_LOGIN.spParaAccName, accName);
            p.Add(SP_ACCOUNT_LOGIN.spParaAccPass, pass);
            try
            {
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp =
                        sqlConnection.Query<DBOAccount>(SP_ACCOUNT_LOGIN.spName, p,
                            commandType: CommandType.StoredProcedure);
                    IEnumerable<DBOAccount> dboAccounts = tmp as IList<DBOAccount> ?? tmp.ToList();
                    return !dboAccounts.Any() ? null : dboAccounts.ToList().First();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 通过AccountID主键来获取一个DBOAccount实例
        /// </summary>
        /// <param name="id">账户的主键</param>
        /// <returns>若登陆成功则返回一个DBOAccount实例，若失败(密码或账户不匹配)则返回null.若出现其他问题(数据库端)则返回exception中的字符串信息</returns>
        public dynamic GetAccountByID(string id)
        {
            var p = new DynamicParameters();
            p.Add(SP_ACCOUNT_GETBYID.accID, id);
            using (sqlConnection)
            {
                sqlConnection.Open();
                var tmp = sqlConnection.Query<DBOAccount>(SP_ACCOUNT_GETBYID.spName, p,
                    commandType: CommandType.StoredProcedure);
                return tmp.ToList().First();
            }
        }

        /// <summary>
        /// 创建一个新的推送信息，请注意和该推送信息关联的多媒体资源（图片等）需要和本方法的返回值同时插入到PushRes表当中。
        /// </summary>
        /// <param name="Push">推送信息临时实体类</param>
        /// <returns>若创建成功则返回新创建的推送信息在Push表中的主键值，如果失败则返回null值，如果出现数据库端错误则返回抛出的异常的字符串表达</returns>
        public dynamic PushNew(DBOPush Push)
        {
            var p = new DynamicParameters();
            p.Add(SP_PUSH_NEW.spType, Push.Push_Type);
            p.Add(SP_PUSH_NEW.spAppro, Push.Push_Approval);
            p.Add(SP_PUSH_NEW.spAct, Push.Push_Active);
            p.Add(SP_PUSH_NEW.spMsg, Push.Push_Msg);
            p.Add(SP_PUSH_NEW.spPubDate, Push.Push_Pub_Date);
            p.Add(SP_PUSH_NEW.spTntID, Push.Acc_ID);
            p.Add(SP_PUSH_NEW.spPushName, Push.Push_Name);
            p.Add(SP_PUSH_NEW.spProdID, Push.Push_Prod_ID);
            p.Add(SP_PUSH_NEW.spStart, Push.Push_Start);
            p.Add(SP_PUSH_NEW.spEnd, Push.Push_End);
            try
            {
                switch ((DBOPush.PushTypes) Enum.Parse(typeof (DBOPush.PushTypes), Push.Push_Type))
                {
                    case DBOPush.PushTypes.NewProd:

                        break;
                    case DBOPush.PushTypes.Discount:
                        p.Add(SP_PUSH_NEW.spStart, Push.Push_Start);
                        p.Add(SP_PUSH_NEW.spEnd, Push.Push_End);
                        break;
                    case DBOPush.PushTypes.Activities:
                        break;
                    default:
                        throw new Exception("pushType字符串不符合既定要求，应为[NewProd],[Discount]中的一种");
                }
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp =
                        sqlConnection.Query<int>(SP_PUSH_NEW.spName, p, commandType: CommandType.StoredProcedure);
                    return tmp.ToList().First();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 编辑一个已经储存的推送信息，若成功则返回被编辑过的推送信息的实例
        /// </summary>
        /// <param name="Push">当前需要编辑的推送信息的主键ID</param>
        /// <returns>若更新成功则返回被编辑过的推送信息的实例,失败则返回异常信息的字符串</returns>
        public dynamic PushEdit(DBOPush Push)
        {
            sqlConnection = new SqlConnection(connectionString.ToString());
            var p = new DynamicParameters();
            p.Add(SP_PUSH_EDIT.spType, Push.Push_Type);
            p.Add(SP_PUSH_NEW.spAppro, Push.Push_Approval);
            p.Add(SP_PUSH_NEW.spAct, Push.Push_Active);
            p.Add(SP_PUSH_NEW.spMsg, Push.Push_Msg);
            p.Add(SP_PUSH_NEW.spPubDate, Push.Push_Pub_Date);
            p.Add(SP_PUSH_NEW.spPushName, Push.Push_Name);
            p.Add(SP_PUSH_NEW.spProdID, Push.Push_Prod_ID);
            p.Add(SP_PUSH_NEW.spStart, Push.Push_Start);
            p.Add(SP_PUSH_NEW.spEnd, Push.Push_End);
            p.Add(SP_PUSH_EDIT.spID, Push.Push_ID);
            p.Add(SP_PUSH_NEW.spStart, Push.Push_Start);
            p.Add(SP_PUSH_NEW.spEnd, Push.Push_End);
            try
            {
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<int>(SP_PUSH_EDIT.spName, p, commandType: CommandType.StoredProcedure);
                    return tmp.ToList().First();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 当推送信息被创建完，该推送相关多媒体资源被上传到服务器有完整的URL之后，再调用此方法将
        /// 多媒体资源和推送关联到ResPush表中
        /// </summary>
        /// <param name="pushID">推送信息的主键ID</param>
        /// <param name="path">多媒体资源的绝对URl</param>
        /// <returns>Insert成功之后将返回新插入的多媒体文件的DBOPushRes实例，失败则返回null值，若出现数据内部问题则返回异常的字符串表达</returns>
        public dynamic UploadRes(int pushID, string path)
        {
            sqlConnection = new SqlConnection(connectionString.ToString());
            var p = new DynamicParameters();
            p.Add(SP_RES_UPLOAD.spID, pushID);
            p.Add(SP_RES_UPLOAD.spPath, path);
            try
            {
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<DBOPushRes>(SP_RES_UPLOAD.spName, p,
                        commandType: CommandType.StoredProcedure);
                    return tmp.ToList().First();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }


        /// <summary>
        /// 用于获取和推送主键(PushID)关联的多媒体文件信息的集合.
        /// </summary>
        /// <param name="pushID">推送信息的主键</param>
        /// <returns>若成功则返回一个DBOPushRes对象的IList集合，失败则返回空对象。如果出现其他内部错误则返回异常的字符串表达。</returns>
        public dynamic GetResByPush(string pushID)
        {
            try
            {
                sqlConnection = new SqlConnection(connectionString.ToString());
                var p = new DynamicParameters();
                p.Add(SP_RES_GETBYPUSH.spPushID, int.Parse(pushID));
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<DBOPushRes>(SP_RES_GETBYPUSH.spName, p,
                        commandType: CommandType.StoredProcedure);
                    return !tmp.Any() ? null : tmp.ToList();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 用于获取和租户主键(Acc_ID)关联的推送信息列表
        /// </summary>
        /// <param name="id">租户账户的主键</param>
        /// <returns>如果该租户有推送信息，则返回推送信息实例DBOPush的IList集合，如没有则返回空表。如果出现数据库异常或者其他内部错误则返回异常的字符串表达。</returns>
        public dynamic GetPushByID(string id)
        {
            try
            {
                var p = new DynamicParameters();
                p.Add(SP_PUSH_GETBYID.tntID, int.Parse(id));
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<DBOPush>(SP_PUSH_GETBYID.spName, p,
                        commandType: CommandType.StoredProcedure);
                    return tmp.ToList();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 用于获取和租户主键(Acc_ID)关联的推送信息中处于active状态的个体
        /// </summary>
        /// <param name="id">租户账户的主键</param>
        /// <returns>如果该租户有处于激活状态中的推送信息，则返回推送信息实例DBOPush的IList集合，如没有则返回空表。如果出现数据库异常或者其他内部错误则返回异常的字符串表达。</returns>
        public dynamic GetActivePushByID(string id)
        {
            try
            {
                var p = new DynamicParameters();
                p.Add(SP_PUSH_GETBYID.tntID, int.Parse(id));
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<DBOPush>(SP_PUSH_GETACTIVE.spName, p,
                        commandType: CommandType.StoredProcedure);
                    return tmp.ToList();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 更改推送的激活状态，可更改为ActiveStatus枚举类中的任意一种
        /// </summary>
        /// <param name="id">推送的主键ID</param>
        /// <param name="status">更改的激活状态值，将为DBOPush的ActiveStatus枚举中的三种</param>
        /// <returns>若成功则返回0，失败则返回异常信息的字符串表达</returns>
        public dynamic UpdatePushActiveStatus(string id, DBOPush.ActiveStatus status)
        {
            try
            {
                var p = new DynamicParameters();
                p.Add(SP_PUSH_UPDATEACT.pushID, int.Parse(id));
                p.Add(SP_PUSH_UPDATEACT.status, status.ToString());
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<int>(SP_PUSH_UPDATEACT.spName, p,
                        commandType: CommandType.StoredProcedure);
                    return tmp.ToList().First();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 根据推送信息的主键返回一个包含该推送信息相关项的实例
        /// </summary>
        /// <param name="id">推送的主键</param>
        /// <returns>若成功则返回一个推送信息的实例，失败则返回异常信息的字符串表达</returns>
        public dynamic GetPushByPushID(string id)
        {
            try
            {
                var p = new DynamicParameters();
                p.Add(SP_PUSH_GETBYPUSHID.pushID, int.Parse(id));
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<DBOPush>(SP_PUSH_GETBYPUSHID.spName, p,
                        commandType: CommandType.StoredProcedure);
                    return tmp.ToList().First();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 根据推送资源的主键来更新推送资源（图片)的绝对路径信息。
        /// </summary>
        /// <param name="id">推送信息的主键</param>
        /// <param name="newPath">新的绝对路径</param>
        /// <returns>若成功则返回更改过后的推送资源(DBOPushRes)实例，失败则返回异常信息的字符串表达</returns>
        public dynamic UpdateResByResID(string id, string newPath)
        {
            try
            {
                var p = new DynamicParameters();
                p.Add(SP_RES_UPDATE.pidcID, int.Parse(id));
                p.Add(SP_RES_UPDATE.newPath, newPath);
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<DBOPushRes>(SP_PUSH_UPDATEACT.spName, p,
                        commandType: CommandType.StoredProcedure);
                    return tmp.ToList().First();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 获取所有DBOStore的实体集合
        /// </summary>
        /// <returns>若成功则返回一个DBOStore的实例类集合，失败则返回异常信息的字符串表达</returns>
        public dynamic GetAllStore()
        {
            try
            {
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<DBOStore>(SP_STORE_GETALL.spName,
                        commandType: CommandType.StoredProcedure);
                    return tmp.ToList();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }

        /// <summary>
        /// 根据Acc_ID在DBOAccount中的主键关联来获取单个的DBOStore的类实体
        /// </summary>
        /// <param name="id">Acc_ID主键</param>
        /// <returns>若成功则返回一个DBOStore的实例，失败则返回异常信息的字符串表达</returns>
        public dynamic GetStoreByAcc(string id)
        {
            try
            {
                var p = new DynamicParameters();
                p.Add(SP_STORE_GETBYACC.accID, int.Parse(id));
                using (sqlConnection)
                {
                    sqlConnection.Open();
                    var tmp = sqlConnection.Query<DBOStore>(SP_STORE_GETBYACC.spName, p,
                        commandType: CommandType.StoredProcedure);
                    return tmp.ToList().First();
                }
            }
            catch (Exception e)
            {
                return e.ToString();
            }
        }
    }
}